/*
	SELECT DISTINCT PS_TYPE,PS_TYPE_NAME FROM PROCESS_STEP
	Usage		:exec sp_load_all_scrapped_lots_by_process_type '0', '0','All Vendors','0','0','0','XLREPORT','0', 2
	select * from SCRAPPED_LOTS
*/
alter PROC sp_load_all_scrapped_lots_by_process_type

@PROCESS_TYPE	VARCHAR(50),
@PROCESSSTEP_ID	VARCHAR(500),
@VENDOR		VARCHAR(50),
@LOT_CATEGORY	VARCHAR(50),
@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@REPORT_TYPE	VARCHAR(50),
@DEFAULT_PROMPT	VARCHAR(50),
@IS_NONVALUE_INVENTORY INT 
AS
BEGIN
	CREATE TABLE #SCRAP_RESULTS
		([LOT_ID] INT,[SCRAP DIE QTY] INT, [SCRAP WAFER QTY] INT,[WAFER IDS] VARCHAR(255),[SCRAPPED DATE] DATETIME,
		 [COMMENTS] VARCHAR(255),[FAB_LOT_NUMBER] VARCHAR(50),[LOT NUMBER] VARCHAR(50),[LOT STATUS] VARCHAR(50),
		 [LOT CATEGORY] VARCHAR(50),[PART NUMBER] VARCHAR(50),[BASE PRODUCT] VARCHAR(50),[PRODUCT TYPE ID] VARCHAR(50),
		 [PROCESS TYPE NAME] VARCHAR(50),[PROCESS] VARCHAR(50),[PS TYPE ID] INT,[VENDOR] VARCHAR(50), [PROCESSSTEP_ID] INT, 
		 [MKTG PART NUMBER] VARCHAR(50), [SCRAP TYPE] VARCHAR(50), [COST] NUMERIC(18,4) ) 

	DECLARE @SQLSTRING 		NVARCHAR(4000)
	
	SET @SQLSTRING = N'INSERT INTO #SCRAP_RESULTS
		SELECT 
			SL.LOT_ID, SL_SCRAPPED_DIE_QTY,SL_SCRAPPED_WAFER_QTY,SL_SCRAPPED_WAFER_IDS,SL_SCRAPPED_DATE,
			CONVERT(VARCHAR(255),SL_COMMENTS) AS SL_COMMENTS,FAB_LOT_NUMBER,LOT_NUMBER,LOT_CURRENT_STATUS,
			LOT_CATEGORY,PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
			) AS BASE_PRODUCT,	
			(CASE 
				WHEN P_TYPE IS NULL THEN 1
				WHEN P_TYPE LIKE LTRIM(RTRIM(''BOARD'')) THEN 2
				WHEN P_TYPE LIKE LTRIM(RTRIM(''KIT'')) THEN 3
			END) AS PRODUCT_TYPE_ID,PS_TYPE_NAME,PS_TYPE,
			(CASE 
				WHEN PS_TYPE_NAME = ''FAB'' THEN 1
				WHEN PS_TYPE_NAME = ''Wafer Store'' THEN 2
				WHEN PS_TYPE_NAME = ''SORT'' THEN 3
				WHEN PS_TYPE_NAME = ''Die Bank'' THEN 4
				WHEN PS_TYPE_NAME = ''Assembly'' THEN 5
				WHEN PS_TYPE_NAME = ''TEST'' THEN 6
				WHEN PS_TYPE_NAME = ''Class Store'' THEN 7
				WHEN ( PS_TYPE_NAME = ''Mark & Pack'' OR PS_TYPE_NAME = ''Mark &amp; Pack'' ) THEN 8
				WHEN ( PS_TYPE_NAME = ''Finished Goods'' OR PS_TYPE_NAME = ''FG'' ) THEN 9
			  END) AS PS_TYPE_ID,L_VENDOR_NAME,PS.PROCESSSTEP_ID, '''',
			(CASE 
				WHEN IS_NONVALUE_INVENTORY = 0 THEN ''Non Valued WIp''
				WHEN IS_NONVALUE_INVENTORY = 1 THEN ''SCRAPED''
			  END) AS SCRAP_TYPE, 0.0
		FROM 
			SCRAPPED_LOTS SL,LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS,LOCATION L
		WHERE 
			SL.LOT_ID = LT.LOT_ID 
			AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
			AND LT.LOCATION_ID = L.LOCATION_ID '
				
	IF @PROCESS_TYPE = '0'
	BEGIN
		IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0' )
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND P.P_PARENT_PRODUCT_ID IN ( ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( '''+@PROCESSSTEP_ID+''' ) )  )'
		END	
	END
	ELSE--IF @PROCESS_TYPE <> '0'
	BEGIN
		IF @PROCESS_TYPE = 'Die Bank'
		BEGIN
			SET @PROCESS_TYPE = 'SORT'
		END

		SET @SQLSTRING = @SQLSTRING + ' AND PS.PS_TYPE = '''+@PROCESS_TYPE+''''

		IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0' )
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND P.PROCESSSTEP_ID IN ( ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( '''+@PROCESSSTEP_ID+''' ) )  )'
		END
	END
	
	IF ( @VENDOR IS NOT NULL AND ( @VENDOR <> '0' and @VENDOR <> 'All Vendors' ) )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L_VENDOR_NAME = '''+@VENDOR+''''
	END
	IF ( @LOT_CATEGORY IS NOT NULL AND @LOT_CATEGORY <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CATEGORY = '''+@LOT_CATEGORY+''''
	END
	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING +' AND SL_SCRAPPED_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + +' AND SL_SCRAPPED_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
	IF ( @IS_NONVALUE_INVENTORY IS NOT NULL and @IS_NONVALUE_INVENTORY = 1  )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + +' AND IS_NONVALUE_INVENTORY = 1' 
	END
	IF ( @IS_NONVALUE_INVENTORY IS NOT NULL and @IS_NONVALUE_INVENTORY = 0  )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + +' AND IS_NONVALUE_INVENTORY = 0' 
	END

	SET @SQLSTRING = @SQLSTRING + ' ORDER BY BASE_PRODUCT,PART_NUMBER,PS_TYPE_ID, SL_SCRAPPED_DATE '
	
	EXEC (@SQLSTRING)

	--TO UPDATE MKTG PART NUMBER AND STANDARD COST
	DECLARE @PROCESSSTEP_CURSOR CURSOR
	DECLARE @SCRAP_PROCESSTEP_ID INT
	DECLARE @SORT_PROCESSSTEP_ID INT
	DECLARE @MKTG_PART_NUMBER VARCHAR(50)
	DECLARE @CURR_QUARTER_NO INT
	DECLARE @CURRENT_YEAR INT
	DECLARE @SCRAPE_DATE DATETIME
	DECLARE @STD_COST NUMERIC(18,4)
	DECLARE @PS_TYPE_NAME VARCHAR(50)

	SET @PROCESSSTEP_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID, [PROCESS TYPE NAME], [SCRAPPED DATE] FROM #SCRAP_RESULTS
	OPEN @PROCESSSTEP_CURSOR 
	FETCH NEXT FROM @PROCESSSTEP_CURSOR INTO @SCRAP_PROCESSTEP_ID, @PS_TYPE_NAME, @SCRAPE_DATE
	WHILE @@FETCH_STATUS = 0
    BEGIN
		/*
		Because scrap of after sort wafers is actually Die bank die
		We need to get the cost of Die bank rather cost of Sort

		IF @PS_TYPE_NAME = 'SORT'
		BEGIN
			SET @SORT_PROCESSSTEP_ID = @SCRAP_PROCESSTEP_ID
			SELECT TOP 1 @SCRAP_PROCESSTEP_ID = ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @SCRAP_PROCESSTEP_ID,1) WHERE PS_TYPE_NAME = 'Die Bank'

			UPDATE #SCRAP_RESULTS SET [PROCESSSTEP_ID] = @SCRAP_PROCESSTEP_ID, [PROCESS] = 'Die Bank' WHERE [PROCESSSTEP_ID] = @SORT_PROCESSSTEP_ID
		END
		*/
		SELECT @CURR_QUARTER_NO = CQC_QUARTER_NO, @CURRENT_YEAR = YEAR( CQC_START_DATE ) FROM COMPANY_QUARTERLY_CALENDAR 
			WHERE @SCRAPE_DATE >= CQC_START_DATE AND @SCRAPE_DATE <= CQC_END_DATE

		SELECT @STD_COST = (CASE WHEN @CURR_QUARTER_NO = 1 THEN Q1_Standard_Cost WHEN @CURR_QUARTER_NO = 2 THEN Q2_Standard_Cost WHEN @CURR_QUARTER_NO = 3 THEN Q3_Standard_Cost ELSE Q4_STANDARD_COST END) FROM Update_View_Cost_Table WHERE ProcessStep_ID = @SCRAP_PROCESSTEP_ID and YEAR(FIN_YEAR) = @CURRENT_YEAR

		IF ( @PS_TYPE_NAME != 'FAB' AND @PS_TYPE_NAME != 'Wafer Store' AND @PS_TYPE_NAME != 'SORT' )
		BEGIN
			UPDATE #SCRAP_RESULTS SET [COST] = [SCRAP DIE QTY]*@STD_COST  WHERE PROCESSSTEP_ID = @SCRAP_PROCESSTEP_ID AND [SCRAPPED DATE] = @SCRAPE_DATE
		END
		ELSE
		BEGIN
			UPDATE #SCRAP_RESULTS SET [COST] = [SCRAP WAFER QTY]*@STD_COST  WHERE PROCESSSTEP_ID = @SCRAP_PROCESSTEP_ID AND [SCRAPPED DATE] = @SCRAPE_DATE
		END
		

		IF ( @PS_TYPE_NAME != 'FAB' AND @PS_TYPE_NAME != 'Wafer Store' AND @PS_TYPE_NAME != 'SORT' AND @PS_TYPE_NAME != 'Die Bank' )
		BEGIN
			SELECT @MKTG_PART_NUMBER = P_MARKETING_PART_NUMBER FROM PRODUCT WHERE PROCESSSTEP_ID = (SELECT TOP 1 PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@SCRAP_PROCESSTEP_ID,1) WHERE PS_TYPE_NAME = 'FINISHED GOODS')
			UPDATE #SCRAP_RESULTS SET [MKTG PART NUMBER] = @MKTG_PART_NUMBER  WHERE PROCESSSTEP_ID = @SCRAP_PROCESSTEP_ID
		END

		
		SET @SCRAP_PROCESSTEP_ID = 0
		SET @MKTG_PART_NUMBER = NULL
		SET @PS_TYPE_NAME = NULL
		SET @SCRAPE_DATE = NULL
		SET @STD_COST  = NULL
		FETCH NEXT FROM @PROCESSSTEP_CURSOR INTO @SCRAP_PROCESSTEP_ID, @PS_TYPE_NAME, @SCRAPE_DATE
	 END
	CLOSE @PROCESSSTEP_CURSOR
	DEALLOCATE @PROCESSSTEP_CURSOR

	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		SELECT 
			[PART NUMBER],[BASE PRODUCT],[MKTG PART NUMBER], [FAB_LOT_NUMBER],[LOT NUMBER],[PROCESS],[VENDOR],
			[SCRAPPED DATE],[SCRAP WAFER QTY],[SCRAP DIE QTY],[SCRAP TYPE],[LOT CATEGORY],--[LOT STATUS],
			[WAFER IDS],[COST],[COMMENTS]
		FROM
			#SCRAP_RESULTS
		ORDER BY
			[PRODUCT TYPE ID],[BASE PRODUCT],[PS TYPE ID],[PART NUMBER],[FAB_LOT_NUMBER]
	END
	ELSE
	BEGIN
		SELECT 
			[LOT_ID],[SCRAP DIE QTY],[SCRAP WAFER QTY],[WAFER IDS],[SCRAP TYPE][SCRAPPED DATE],[COMMENTS],
			[FAB_LOT_NUMBER],[LOT NUMBER],[LOT STATUS],[LOT CATEGORY],[PART NUMBER],[BASE PRODUCT],
			[PRODUCT TYPE ID],[PROCESS TYPE NAME],[PROCESS],[PS TYPE ID],[VENDOR]
		FROM
			#SCRAP_RESULTS
		ORDER BY
			[PRODUCT TYPE ID],[BASE PRODUCT],[PS TYPE ID],[PART NUMBER],[FAB_LOT_NUMBER]
	END
END
